package cc.mrbird.febs.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.ArrayList;
import java.util.List;
/*
 * @Author HBH
 * @Description excel导出工具类
 * @Date 2022/4/18
 **/
public class ExportUtil {

    /*
     * @Author HBH
     * @Description 实现简单的导出
     * @Date 2022/4/24
     **/
    public static Workbook simpleExport(String sheetName, List<JSONObject> dataList, List<String> titles, List<String> keys) {
        if (StringUtils.isEmpty(keys))
            keys = titles;
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName);
        Row row;
        Cell cell;

        Font fontTitle = wb.createFont();
        Font fontFirstRow = wb.createFont();
        Font fontContent = wb.createFont();
        Font fontPoint = wb.createFont();

        fontTitle.setFontName("方正小标宋简体");
        fontTitle.setFontHeightInPoints((short) 22);
        fontFirstRow.setFontName("黑体");
        fontFirstRow.setFontHeightInPoints((short) 11);
        fontContent.setFontName("仿宋_GB2312");
        fontContent.setFontHeightInPoints((short) 11);
        fontPoint.setFontName("黑体");
        fontPoint.setFontHeightInPoints((short) 11);

        CellStyle styleTitle = wb.createCellStyle();    // 标题样式
        CellStyle styleFirstRow = wb.createCellStyle(); // 字段名样式
        CellStyle styleContent = wb.createCellStyle();  // 内容样式

        styleTitle.setAlignment(HorizontalAlignment.CENTER);
        styleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        styleTitle.setFont(fontTitle);

        styleFirstRow.setAlignment(HorizontalAlignment.CENTER);
        styleFirstRow.setVerticalAlignment(VerticalAlignment.CENTER);
        styleFirstRow.setFont(fontFirstRow);

        // styleContent.setAlignment(HorizontalAlignment.CENTER);
        styleContent.setVerticalAlignment(VerticalAlignment.CENTER);
        styleContent.setFont(fontContent);

        row = sheet.createRow(0);
        row.setHeight((short) (22.50 * 20));//设置行高

        for (int i = 0; i < titles.size(); i++) {
            cell = row.createCell(i);
            cell.setCellStyle(styleFirstRow);
            cell.setCellValue(titles.get(i));
        }

        //遍历所获取的数据
        for (int i = 0; i < dataList.size(); i++) {
            row = sheet.createRow(i + 1);
            JSONObject student = dataList.get(i);

            for (int j = 0; j < keys.size(); j++) {
                cell = row.createCell(j);
                cell.setCellStyle(styleContent);
                Object value = student.get(keys.get(j));
                cell.setCellValue(value != null ? value.toString() : "");
            }
        }

        //列宽自适应
        for (int i = 0; i <= titles.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        return wb;
    }

    /*
     * @Author HBH
     * @Description 获取excel导出下标索引
     * @Date 2022/4/24
     **/
    public static List<String> getColIndex(Integer size) {
        String az = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        List<String> colIndex = new ArrayList<>();
        String pre = "";
        int cnt = 0, k = 0;
        for (int i = 0; i < size; i++) {
            if (k == 26) {
                pre = az.charAt(cnt) + "";
                cnt++;
                k = 0;
            }
            colIndex.add(pre + az.charAt(k) + "");
            k++;
        }
        return colIndex;
    }

    /**
     * 删除指定列
     *
     * @param sheet
     * @param columnToDelete
     */
    public static void deleteColumn(XSSFSheet sheet, int columnToDelete) {
        for (int rId = 1; rId <= sheet.getLastRowNum(); rId++) {
            Row row = sheet.getRow(rId);
//            if (row == null) {
//                continue;
//            }
            for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
                Cell cOld = row.getCell(cID);
                if (cOld != null) {
                    // 删除批注
                    if (cOld.getCellComment() != null) {
                        cOld.removeCellComment();
                    }
                    row.removeCell(cOld);
                }
                Cell cNext = row.getCell(cID + 1);
                if (cNext != null) {
                    Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
                    cloneCell(cNew, cNext);
                    if (rId == 0) {
                        sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
                    }
                }
            }
        }
    }



    /**
     * 右边列左移
     *
     * @param cNew
     * @param cOld
     */
    public static void cloneCell(Cell cNew, Cell cOld) {
        cNew.setCellComment(cOld.getCellComment());
        cNew.setCellStyle(cOld.getCellStyle());

        if (CellType.BOOLEAN == cNew.getCellTypeEnum()) {
            cNew.setCellValue(cOld.getBooleanCellValue());
        } else if (CellType.NUMERIC == cNew.getCellTypeEnum()) {
            cNew.setCellValue(cOld.getNumericCellValue());
        } else if (CellType.STRING == cNew.getCellTypeEnum()) {
            cNew.setCellValue(cOld.getStringCellValue());
        } else if (CellType.ERROR == cNew.getCellTypeEnum()) {
            cNew.setCellValue(cOld.getErrorCellValue());
        } else if (CellType.FORMULA == cNew.getCellTypeEnum()) {
            cNew.setCellValue(cOld.getCellFormula());
        }
    }
}
